﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LinqToExcel;
using System.Linq.Expressions;
using System.Windows.Forms;
using System.Reflection;

namespace MyVoteMVC.BackstageManagement.Extensions
{
    public class ExcelHelper
    {
        private ExcelQueryFactory excel;


        public ExcelHelper()
        {
            //excel = new ExcelQueryFactory(filePath);
        }

        public ExcelHelper(string filePath)
        {
             excel = new ExcelQueryFactory(filePath);
        }

        public List<string> GetWorksheetNames()
        {
            return excel.GetWorksheetNames().ToList();
        }

        public void AddMapping<TModel>(Expression<Func<TModel, object>> property, string column)
        {
            if (excel != null)
            {
                excel.AddMapping<TModel>(property, column);
            }
        }

        /// <summary>
        /// sheet1
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <returns></returns>
        public IList<TModel> Query<TModel>()
        {
            if (excel != null)
            {
                return excel.Worksheet<TModel>().ToList();
            }

            return null;
        }

        /// <summary>
        /// 指定sheetname
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public IList<TModel> Query<TModel>(string sheetName)
        {
            if (excel != null)
            {
                return excel.Worksheet<TModel>(sheetName).ToList();
            }

            return null;
        }

        /// <summary>
        /// 指定第sheetIndex个sheet
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public IList<TModel> Query<TModel>(int sheetIndex)
        {
            if (excel != null)
            {
                return excel.Worksheet<TModel>(sheetIndex).ToList();
            }

            return null;
        }

        /// <summary>
        /// 需要确定导出的model
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="list"></param>
        public void Write(IQueryable list)
        {
            SaveFileDialog sfd = new SaveFileDialog()
            {
                DefaultExt = "csv",
                Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*",
                FilterIndex = 1
            };
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                using (System.IO.Stream stream = sfd.OpenFile())
                {
                    using (System.IO.StreamWriter writer = new System.IO.StreamWriter(stream, Encoding.Unicode, 30))
                    {
                        string data = FormatExcelData(list);//调用函数
                        if (data == string.Empty) return;
                        byte[] tmp;
                        tmp = Encoding.Unicode.GetBytes(data);
                        writer.Write(data);
                        writer.Close();
                    }
                    stream.Close();
                }
            }
        }

        private string FormatExcelData(IQueryable list)
        {
            
            System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();
            List<string> keys = new List<string>();
            foreach (object result in list)
            {
                PropertyInfo[] properties = result.GetType().GetProperties();
                keys = (from property in properties
                                     select property.Name).ToList();
                break;
            }
          
            //header
            List<string> headers = new List<string>();
            foreach (string header in keys)
            {
                headers.Add(FormatCSVField(header, (keys.IndexOf(header) == 0)));
            }
            strBuilder.Append(String.Join("", headers.ToArray())).Append("\t\n");

            //data
            foreach (object result in list)
            {
                List<string> data = new List<string>();
                foreach (string key in keys)
                {
                    string nvalue = "";
                    try
                    {
                        nvalue = result.GetType().GetProperty(key).GetValue(result, null).ToString();
                    }
                    catch{
                        nvalue = "";
                    }

                    data.Add(FormatCSVField(nvalue, (keys.IndexOf(key) == 0)));
                }
                strBuilder.Append(String.Join("", data.ToArray())).Append("\t\n");
            }

            return strBuilder.ToString();

        }

        private string FormatCSVField(string data, bool first)
        {
            if (first)
                return data.Replace("\"", "\t\n");
            else
                return String.Format("\t{0}", data.Replace("\"", "\t\n"));
        }
    }
}
